import pymysql
import utils

def test_insert():
    try:
        #1、创建连接
        con = pymysql.connect(host="localhost",user="19dsj",
                               password="123",db="19dsj_test",charset="utf8")
        # print(con)
        #2、创建sql,%s表示预编译，为了防止sql的注入攻击
        sql = "insert into t_user(username,password,nickname,born) value (%s,%s,%s,%s)"
        #3、创建cursor
        cursor = con.cursor()
        #4、运行sql，参数是一个元组对象，设置的是预编译中值
        cursor.execute(sql,("xiaoli","123","小李","1990-2-2"))
        #5、提交事务，只有数据修改的操作才有必要提交事务，注入插入，修改，删除
        con.commit()
    except pymysql.MySQLError as e:
        print(e)
        con.rollback()
    finally:
        #6、关闭cursor和con
        cursor.close()
        con.close()

def test_update():
    try:
        con = utils.get_conn()
        #使用with创建的对象，在with结束之后会自动恢复到原来状态，不能替换finally
        with con.cursor() as cursor:
            sql = "update t_user set username='zhang' where id=%s"
            cursor.execute(sql,4)
            con.commit()
    except pymysql.MySQLError as e:
        print(e)
        con.rollback()

def test_del(id):
    try:
        con = utils.get_conn()
        with con.cursor() as cursor:
            sql = "delete from t_user where id=%s"
            cursor.execute(sql,id)
            con.commit()
    except pymysql.MySQLError as e:
        print(e)
        con.rollback()
    finally:
        utils.close(con)

def test_load(id):
    try:
        con = utils.get_conn()
        print(con)
        cursor = con.cursor()
        sql = "select * from t_user where id=%s"
        cursor.execute(sql,id)
        #抓取一个数据,如果有多条数据，只会获取第一条数据
        result = cursor.fetchone()
        print(result)
    except pymysql.MySQLError as e:
        print(e)
        con.rollback()
    finally:
        cursor.close()
        utils.close(con)

def test_list():
    try:
        con = utils.get_conn()
        cursor = con.cursor()
        sql = "select * from t_user"
        cursor.execute(sql)
        #cursor的fetchall方法可以把数据抓取出来，得到是一个元组，元组里面存储也是按照投影排序的元组
        results = cursor.fetchall() #直接把查询出来的值放到了一个元组中
        print(type(results))
        for s in results:
            print(s)
    except pymysql.MySQLError as e:
        print(e)
    finally:
        cursor.close()
        utils.close(con)

def test_login(username,password):
    try:
        con = utils.get_conn()
        cursor = con.cursor()
        # admin'"# 如果直接在sql语句中插入变量，有可能会受到sql的注入攻击，所以在实际的开发中，一般不能使用直接添加值的方式
        sql = "select count(*) from t_user where username=%s and password=%s"
        # sql = "select count(*) from t_user where username='admin'"#' and password='"+password+"'"
        cursor.execute(sql,(username,password))
        result = cursor.fetchone()
        print(result)
    finally:
        cursor.close()
        con.close()


if __name__ == '__main__':
    # test_insert()
    # test_update()
    # test_load()
    # test_del()
    # test_list()
    test_login('admin','123')